package www.lagou.app;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import www.lagou.entity.Account;
import www.lagou.utils.DruidUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.junit.Test;
public class TestTransaction {


    /**
     * 按照步骤 实现卡号：1122334455 向 55443332211 转账 5000 元的操作；
     */
    @Test
    public void testTransaction() throws Exception {

        //a) 使用连接池创建QueryRunner对象；
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //b) 判断转出方是否有足够余额，如果不足，提示信息：”余额不足！”，并结束程序；
        //2.编写SQL查询
        String sql = "select * from account where card = ?";

        //3.执行查询

        Account account = qr.query(sql, new BeanHandler<Account>(Account.class), "1122334455");
        System.out.println("您的账户信息是: " + account);

        if (account.getBalance() >= 5000) {

            Connection connection = null;
            try {
                //c) 通过卡号 进行转账的操作；
                //1.获取连接
                connection = DruidUtils.getConnection();

                //2.开启事务
                connection.setAutoCommit(false);
                System.out.println("转账开始...");
                //3.卡号 1122334455 账户 - 5000
                sql = "update account set balance = balance - ? where card = ?";

                //设置占位符的参数
                Object[] param = {5000, "1122334455"};
                qr.update(sql,param);
                System.out.println("您卡号为1122334455的账户已转出5000");
                //4.卡号 55443332211 账户 + 5000
                sql = "update account set balance = balance + ? where card = ?";
                //设置占位符的参数
                Object[] param1 = {5000, "55443332211"};
                qr.update(sql,param1);
                System.out.println("卡号为55443332211的账户增加5000");
                //d) 转账结束后, 将转入、转出记录分别写入到Transaction表中

                //d.1 获取当前转账时间
                Date date = new Date();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String datetime = sdf.format(date);

                //d.2 编写sql 添加装入转出操作数据
                System.out.println("开始添加转账记录,请稍后");
                sql = "insert into transaction values(?,?,?,?,?)";

                //d.3 设置占位符参数
                //d.3.1 设置卡号 1122334455 的转出记录
                Object[] param2 = {null, "1122334455", "转出", 5000, datetime};
                qr.update(sql,param2);
                System.out.println("卡号为1122334455的账户转出5000");
                //d.3.2 设置卡号 55443332211 转入 5000
                Object[] param3 = {null, "55443332211", "转入", 5000, datetime};
                qr.update(sql,param3);
                System.out.println("转账成功");
                DbUtils.commitAndCloseQuietly(connection);
            } catch (SQLException e) {
                e.printStackTrace();
                DbUtils.rollback(connection);
            }
        }else {
            System.out.println("余额不足!");
        }
    }

}
